﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using EntityObjects;

namespace DAL_V1.App_Code.DBBroker
{
    public class SupplierDBBroker
    {
        private SSISDataSet ds;
        private SSISDataSetTableAdapters.SupplierTableAdapter tda;
        private SSISDataSetTableAdapters.SupplierTenderTableAdapter tendertda;
        
        private int index;
        private List<Supplier> sup_list;
        private List<SupplierTender> supTender_list;
        private Supplier sup;
        private SupplierTender supTender;
        private SSISDataSet.SupplierDataTable sup_td;
        private SSISDataSet.SupplierTenderDataTable supTender_td;

         //singleton
        private static bool instance_flag = false;
        private static SupplierDBBroker instance;

        private SupplierDBBroker()
        {
            ds = new SSISDataSet();
            tda = new SSISDataSetTableAdapters.SupplierTableAdapter();
            tendertda = new SSISDataSetTableAdapters.SupplierTenderTableAdapter();

            //tda.Fill(ds.Supplier);
            //tendertda.Fill(ds.SupplierTender);  
        }

        public static SupplierDBBroker getSupplierDBBroker()
        {
            if (!instance_flag)
            {
                instance = new SupplierDBBroker();
                instance_flag = true;

                return instance;
            }
            else
            {
                return instance;
            }
        }

        public List<Supplier> selectAllSupplier()
        {
            tda.Fill(ds.Supplier);
            sup_list = new List<Supplier>();
            for (int i = 0; i < ds.Supplier.Rows.Count; i++)
            {
                sup = new Supplier();
                sup.SupplierId = ds.Supplier[i].SupplierId;
                sup.SupplierName = ds.Supplier[i].SupplierName;
                sup.ContactName = ds.Supplier[i].ContactName;
                sup.PhNo = ds.Supplier[i].PhNo;
                sup.FaxNo = ds.Supplier[i].FaxNo;
                sup.Address = ds.Supplier[i].Address;
                sup.GSTRegNo = ds.Supplier[i].GSTRegNo;
                sup.Status = ds.Supplier[i].Status;

                //Tender
                supTender_list = selectTenderBySupplierId(ds.Supplier[i].SupplierId);
                sup.SupTender_list = supTender_list;

                sup_list.Add(sup);
            }
            return sup_list;
        }

        public int addSupplier(Supplier sup)
        {
            SSISDataSet.SupplierRow newRow = ds.Supplier.NewSupplierRow();
            newRow.SupplierId = sup.SupplierId;
            newRow.SupplierName = sup.SupplierName;
            newRow.ContactName = sup.ContactName;
            newRow.PhNo = sup.PhNo;
            newRow.FaxNo = sup.FaxNo;
            newRow.Address = sup.Address;
            newRow.GSTRegNo = sup.GSTRegNo;
            newRow.Status = sup.Status;
                    
            ds.Supplier.Rows.Add(newRow);
            return tda.Update(ds.Supplier);
        }

        //private int getIndex(String Id)
        //{
        //    SSISDataSet.SupplierRow row = ds.Supplier.FindBySupplierId(Id);
        //    return ds.Supplier.Rows.IndexOf(row);
        //}

        private SSISDataSet.SupplierRow getRowById(String Id)
        {
            tda.Fill(ds.Supplier);
            return ds.Supplier.FindBySupplierId(Id);
        }

        public int updateSupplier(Supplier sup, String Id)
        {
            SSISDataSet.SupplierRow row = getRowById(Id);
            if (row != null)
            {
                if(sup.SupplierName != null && !(sup.SupplierName.Equals("")))
                {
                    row.SupplierName = sup.SupplierName;
                }
                if(sup.ContactName != null && !(sup.ContactName.Equals("")))
                {
                    row.ContactName = sup.ContactName;
                }
                if(sup.PhNo != null && !(sup.PhNo.Equals("")))
                {
                row.PhNo = sup.PhNo;
                }
                if(sup.FaxNo != null && !(sup.FaxNo.Equals("")))
                {
                    row.FaxNo = sup.FaxNo;
                }
                if(sup.Address != null && !(sup.Address.Equals("")))
                {
                    row.Address = sup.Address;
                }
                if(sup.GSTRegNo != null && !(sup.GSTRegNo.Equals("")))
                {
                    row.GSTRegNo = sup.GSTRegNo;
                }
                if(sup.Status != null && !(sup.Status.Equals("")))
                {
                    row.Status = sup.Status;
                }
            }
        
            return tda.Update(ds.Supplier);
        }

        public int deleteSupplier(String Id)
        {
            return tda.DeleteBySupplierId(Id);
        }

        public Supplier selectSupplier(String Id)
        {
            tda.Fill(ds.Supplier);
            SSISDataSet.SupplierRow r = getRowById(Id);
            if (r != null)
            {
                sup = new Supplier();
                sup.SupplierId = r.SupplierId;
                sup.SupplierName = r.SupplierName;
                sup.ContactName = r.ContactName;
                sup.PhNo = r.PhNo;
                sup.FaxNo = r.FaxNo;
                sup.Address = r.Address;
                sup.GSTRegNo = r.GSTRegNo;
                sup.Status = r.Status;

                //Tender
                supTender_list = selectTenderBySupplierId(r.SupplierId);
                sup.SupTender_list = supTender_list;

                return sup;
            }
            else
            {
                return null;
            }
        }

        //Supplier Tender
        public List<SupplierTender> selectAllSupplierTender()
        {
            tendertda.Fill(ds.SupplierTender); 
            supTender_list = new List<SupplierTender>();
            for (int i = 0; i < ds.SupplierTender.Rows.Count; i++)
            {
                supTender = new SupplierTender();
                supTender.ItemId = ds.SupplierTender[i].ItemId;
                supTender.SupplierId = ds.SupplierTender[i].SupplierId;
                supTender.Price = Convert.ToDouble(ds.SupplierTender[i].Price);
                supTender_list.Add(supTender);
            }
            return supTender_list;
        }

        public int addSupplierTender(SupplierTender sup)
        {
            SSISDataSet.SupplierTenderRow newRow = ds.SupplierTender.NewSupplierTenderRow();
            newRow.ItemId = sup.ItemId;
            newRow.SupplierId = sup.SupplierId;
            newRow.Price = Convert.ToDecimal(sup.Price);

            ds.SupplierTender.Rows.Add(newRow);
            return tendertda.Update(ds.SupplierTender);
        }

        //private int getIndex(String itemId,String supplierId)
        //{
        //    SSISDataSet.SupplierTenderRow row = ds.SupplierTender.FindByItemIdSupplierId(itemId, supplierId);
        //    return ds.SupplierTender.Rows.IndexOf(row);
        //}

        private SSISDataSet.SupplierTenderRow getRowById(String supId, String itemId)
        {
            tendertda.Fill(ds.SupplierTender);  
            return ds.SupplierTender.FindByItemIdSupplierId(supId, itemId);
        }

        public int updateSupplierTender(SupplierTender sup, String itemId,String supplierId)
        {
            SSISDataSet.SupplierTenderRow row = getRowById(supplierId, itemId);
            if (row != null)
            {
                if (sup.ItemId != null && !(sup.ItemId.Equals("")))
                {
                    row.ItemId = sup.ItemId;
                }
                if (sup.SupplierId != null && !(sup.SupplierId.Equals("")))
                {
                    row.SupplierId = sup.SupplierId;
                }
                if (sup.Price != null)
                {
                    row.Price = Convert.ToDecimal(sup.Price);
                }
            }
          
            return tendertda.Update(ds.SupplierTender);
        }

        public int deleteSupplierTender(String itemId,String supplierId)
        {
            return tendertda.DeleteBySupIdAndItemId(itemId, supplierId);

        }

        public SupplierTender selectSupplierTender(String itemId,String supplierId)
        {
            tendertda.Fill(ds.SupplierTender); 
            SSISDataSet.SupplierTenderRow r = getRowById(supplierId, itemId);
            if (r != null)
            {
                supTender = new SupplierTender();
                supTender.ItemId = r.ItemId;
                supTender.SupplierId = r.SupplierId;
                supTender.Price = Convert.ToDouble(r.Price);
                return supTender;
            }
            else
            {
                return null;
            }
        }

        public List<SupplierTender> selectTenderBySupplierId(String id)
        {
            tendertda.Fill(ds.SupplierTender); 
            supTender_td = tendertda.GetDataBySupId(id);

            if (supTender_td != null)
            {
                supTender_list = new List<SupplierTender>();
                for (int i = 0; i < supTender_td.Rows.Count; i++)
                {
                    supTender = new SupplierTender();
                    supTender.ItemId = supTender_td[i].ItemId;
                    supTender.SupplierId = supTender_td[i].SupplierId;
                    supTender.Price = Convert.ToDouble(supTender_td[i].Price);

                    supTender_list.Add(supTender);
                }

                return supTender_list;
            }
            else
            {
                return null;
            }
        }
    }
}
